package com.hulu.yec.teach.examplan.service;

import java.util.List;

import com.hulu.yec.common.exception.DaoRuntimeException;
import com.hulu.yec.teach.examplan.common.PageModel;
import com.hulu.yec.teach.examplan.common.util;
import com.hulu.yec.teach.examplan.entity.ExamPlanEntity;

public class ExamPlanDao {

	/**
	 * 分页
	 * 
	 * @param examPlanEntity
	 * @param pageNo
	 * @param pageSize
	 * @param state
	 * @param timed
	 * @param coursename
	 * @param ifmyupdte
	 * @param userId
	 * @param row
	 * @return
	 */
	public static PageModel<ExamPlanEntity> list(ExamPlanEntity examPlanEntity,
			int pageNo, int pageSize, int state, int timed, String coursename,
			int ifmyupdte, int userId) {
		try {
			// 分页查询总条数
			StringBuilder sbgetallRecords = new StringBuilder();
			sbgetallRecords.append("SELECT COUNT(*) ");
			sbgetallRecords.append("FROM exam_plan p, qes_exam_paper c ");
			sbgetallRecords.append("WHERE c.id=p.exam_paper_id ");
			if (" ".equals(coursename) && coursename != null) {
				sbgetallRecords.append(" AND c.name='" + coursename + "' ");
			}
			System.out.println("dao:" + ifmyupdte);
			if (ifmyupdte != 0) {
				if (ifmyupdte == 1) {
					sbgetallRecords.append(" AND p.operate_user_id =" + userId);
				} else if (ifmyupdte == 2) {
					sbgetallRecords.append(" AND p.operate_user_id != "
							+ userId);
				}
			}

			if (state != 0) {
				sbgetallRecords.append(" AND p.state=" + state);
			}
			if (timed != 0) {
				if (timed == 1) {
					sbgetallRecords.append(" AND NOW()<p.exam_time_start ");
				} else {
					sbgetallRecords.append(" AND NOW()>p.exam_time_start ");
				}
			}

			// 分页查询语句
			StringBuilder sb = new StringBuilder();
			sb.append("SELECT p.id, p.exam_time_start,");
			sb.append("p.operate_user_id,p.exam_time_stop,");
			sb.append("p.exam_classroom, c.name, p.state ");
			sb.append("FROM exam_plan p, qes_exam_paper c ");
			sb.append("WHERE c.id=p.exam_paper_id ");
			if (" ".equals(coursename) && coursename != null) {
				sb.append("AND c.name='" + coursename + "' ");
			}
			if (ifmyupdte != 0) {
				if (ifmyupdte == 1) {
					sb.append(" AND p.operate_user_id =" + userId);
				} else if (ifmyupdte == 2) {
					sb.append(" AND p.operate_user_id !=" + userId);
				}
			}
			if (state != 0) {
				sb.append(" AND p.state=" + state);
			}
			if (timed != 0) {
				if (timed == 1) {
					sb.append(" AND NOW()<p.exam_time_start ");
				} else {
					sb.append(" AND NOW()>p.exam_time_start ");
				}
			}

			sb.append(" ORDER BY c.id DESC ");
			sb.append(" limit " + (pageNo - 1) * pageSize + "," + pageSize);

			String sql = sbgetallRecords.toString();
			String sqll = sb.toString();
			return util.executeQuery(sql, sqll, pageNo, pageSize,
					examPlanEntity);
		} catch (ClassNotFoundException e) {
			throw new DaoRuntimeException("数据库操作错误！");
		}
	}

	/**
	 * 详情查询
	 * 
	 * @param id
	 * @return
	 */
	public static List<ExamPlanEntity> DetailList(int id) {
		try {
			String sql = "SELECT p.id, p.exam_time_start, p.exam_time_stop, p.exam_classroom, c.id AS 'id1', c.name AS 'paperName', n.id AS 'id2', n.name AS 'className',m.id AS 'id3', m.name AS 'managerName', p.operate_time, p.state, p.memo FROM exam_plan p, qes_exam_paper c, sys_user m, sys_class n WHERE c.id=p.exam_paper_id AND p.operate_user_id=m.id AND p.to_class_id = n.id AND n.id = p.to_class_id AND p.id="
					+ id;
			return util.executeQuery(sql);
		} catch (ClassNotFoundException e) {
			throw new DaoRuntimeException("数据库操作错误！");
		}
	}

	/**
	 * 补考人员查询
	 * 
	 * @param ids
	 * @return
	 * @throws ClassNotFoundException
	 */
	public static List<ExamPlanEntity> UsersName(String[] ids)
			throws ClassNotFoundException {
		StringBuilder sb = new StringBuilder();
		if (ids.length > 1) {
			sb.append("SELECT id, name FROM sys_user WHERE ");
			sb.append("id=" + ids[1]);
			for (int i = 2; i < ids.length - 1; i++) {
				sb.append(" OR id=" + ids[i]);
			}
			return util.executeQueryUsers(sb.toString());
		} else {
			return null;
		}
	}

	/**
	 * 获取补考人员Id
	 * 
	 * @param id
	 * 
	 * @return
	 */
	public static List<ExamPlanEntity> UsersId(int id) {
		try {
			String sql = "SELECT to_user_ids FROM exam_plan WHERE id=" + id;
			return util.executeQueryUsersId(sql);
		} catch (ClassNotFoundException e) {
			throw new DaoRuntimeException("数据库操作错误！");
		}
	}

	/**
	 * 新增、修改考试计划
	 * 
	 * @param sql
	 * 
	 * @return
	 * @throws ClassNotFoundException
	 */
	public static int AddExplan(String sql) throws ClassNotFoundException {
		return util.executeUpdate(sql);

	}

	/**
	 * 状态的修改
	 * 
	 * @param state
	 * @return
	 * @throws ClassNotFoundException
	 */
	public static int SetateUpdate(String state, String id)
			throws ClassNotFoundException {
		String sql = "UPDATE exam_plan SET state=" + state + " WHERE id=" + id;
		return util.executeUpdate(sql);
	}

}
